# coding: utf-8
"""
使用dialout方式自动外呼的脚本

"""

import uuid
import datetime
import time
import json

from sqlalchemy.sql import text

from common.schedule.seven_moor_celery import celery_app
from common.utils import DB, redis_client
from common.seven_moor import Agent
from common.constant import (
    TEL_X_AREA_MAP, OUTBOUND_USE_VIRTUAL_NUMBER, BUSY_7MOOR_AGENTS
)


@celery_app.task(name='new_seven_moor_outbound_task')
def new_seven_moor_outbound_task():
    """
    add by zhouhl 2018-01-11
    七陌外呼定时任务，执行间隔10秒，程序内判断只在8点至24点之间执行
    执行流程：
        1. 调用七陌接口，查询在线且空闲的座席
        2. 查询这些坐席在本地也为空闲的坐席
        3. 对这些均空闲的坐席推外呼任务
    """

    current_hour = datetime.datetime.now().hour
    if 8 <= current_hour:
        with DB() as session:
            # 获取本地与线上均空闲的坐席
            idle_agent_list = Agent.query_idle_agents()
            print(idle_agent_list)
            idle_agent_list = list(filter(
                lambda x: not redis_client.sismember(BUSY_7MOOR_AGENTS, x),
                idle_agent_list
            ))
            print(idle_agent_list)

            if len(idle_agent_list) > 0:
                new_push_new_outbound(session, idle_agent_list)


def new_push_new_outbound(session, idle_agent_list):
    """
    add by zhouhl 2018-01-11
    根据在线客服与处理中的任务，推送新的外呼任务
    根据配置项，判断是否给外呼任务的号码绑定虚拟号
    """
    QueueStateWaiting = 1

    # schedule_time 比当前时间加1分钟，任务量少的时候尽量做到任务提前
    schedule_time = datetime.datetime.now() + datetime.timedelta(minutes=1)

    # 将5分钟前更新的，queue_state=2的outbound_record修改为queue_state=1
    # 重新推送，防止阻塞自动外呼 add by zhouhl 2018-03-02

    select_timeout_outbound_sql = text("""
        select agent_id, virtual_number_id from outbound_records
        where 
            queue_state = 2
            and updated_at < :updated_at;
    """)

    unbind_timeout_virt_num_sql = text("""
        update virtual_numbers
        set valid=0 where id=:id
    """)

    update_timeout_outbound_sql = text("""
        update
            outbound_records
        set
            queue_state = 10,
            updated_at = UNIX_TIMESTAMP()
        where
            queue_state = 2
            and updated_at < :updated_at;
    """)

    # 按照计划时间查询外呼任务
    select_waiting_outbound_sql = text("""
        select
            id,mobile,need_virtual_number
        from
            outbound_records
        where
            queue_state = :queue_state
            and schedule_time <= :schedule_time
            and call_reason <> 6
        order by
            call_weight asc,
            schedule_time asc,
            created_at asc
        limit :idle_agent_num;
    """)

    # 更新 outbound_record 的 webcall_action_id
    update_outbound_webcall_action_id = text("""
        update
            outbound_records
        set
            webcall_action_id = :webcall_action_id,
            queue_state = 2,
            updated_at = UNIX_TIMESTAMP(),
            agent_id=:agent_id
        where
            id = :id;
    """)

    timeout_updated_at = int(time.time()) - 60 * 5

    # 把超时的坐席重新置闲, 并解绑对应虚拟号, 更新queue_state状态
    timeout_agents = session.execute(
        select_timeout_outbound_sql,
        {
            'updated_at': timeout_updated_at
        }
    ).fetchall()
    for each_timeout_agent in timeout_agents:
        if each_timeout_agent.agent_id:
            redis_client.srem(BUSY_7MOOR_AGENTS, each_timeout_agent.agent_id)
        if each_timeout_agent.virtual_number_id:
            session.execute(
                unbind_timeout_virt_num_sql,
                {
                    'id': each_timeout_agent.virtual_number_id
                }
            )

    session.execute(
        update_timeout_outbound_sql,
        {
            'updated_at': timeout_updated_at
        }
    )

    query_params = dict(
        queue_state=QueueStateWaiting,
        schedule_time=schedule_time,
        idle_agent_num=len(idle_agent_list),
    )
    pending_handle_outbound_list = session.execute(
        select_waiting_outbound_sql,
        query_params
    ).fetchall()

    outbound_use_virtual_number = json.loads(
        redis_client.get(OUTBOUND_USE_VIRTUAL_NUMBER) or 'false')

    pending_handle_outbounds = [
        {
            'id': i.id,
            'mobile': i.mobile,
            'need_virtual_number': i.need_virtual_number,
            'virtual_num': bind_virtual_number(session, i) \
                if outbound_use_virtual_number else '',
        }
        for i in pending_handle_outbound_list
    ]

    if pending_handle_outbounds:
        zip_data = zip(idle_agent_list, pending_handle_outbounds)
        for agent, outbound in zip_data:
            print('*' * 50)
            # 调用七陌外呼接口, 若有虚拟号, 则外呼虚拟号
            action_id = uuid.uuid4().hex
            virtual_number_id = None
            dialout_num = outbound['mobile']
            if outbound['virtual_num']:
                dialout_num = outbound['virtual_num'][0]
                virtual_number_id = outbound['virtual_num'][1]
                print('虚拟号为: {}'.format(outbound['virtual_num'][0]))
            resp = Agent.dialout(agent, dialout_num, action_id)
            print(resp)
            print('auto dialout...')
            print(outbound['mobile'])

            print('*' * 50)
            if resp.get('Succeed'):
                # 更新 outbound 信息
                session.execute(
                    update_outbound_webcall_action_id,
                    {
                        'webcall_action_id': action_id,
                        'id': outbound['id'],
                        'agent_id': agent
                    }
                )
                # 本地坐席置忙
                redis_client.sadd(BUSY_7MOOR_AGENTS, agent)
            else:
                if virtual_number_id:
                    session.execute(
                        unbind_timeout_virt_num_sql,
                        {
                            'id': virtual_number_id
                        }
                    )


def bind_virtual_number(session, outbound_record):
    """
    add by zhouhl 2018-01-12
    根据外呼任务的配置，绑定虚拟号并更新数据库
    1. 先查询该外呼任务的号码是否存在axn关系，需要防止把这些x号再绑定，防止与其>他业务冲突
    2. 查找可用的虚拟号，并绑定axn关系
    3. 更新外呼记录相关信息
    """
    if not outbound_record.need_virtual_number:
        return ''

    mobile = outbound_record.mobile

    # 先查询是否已经有有效的axn虚拟号关系
    find_existed_valid_sql = text("""
        select id, tel_x from virtual_numbers
        where tel_a=:tel_a and tel_b='' and valid=1;
    """)

    # 根据mobile查user信息，获取省份信息
    find_user_sql = text("""
        select
            id, area_id
        from
            user
        where
            mobile = :mobile
            and deleted_at=0;
    """)

    # 查询该mobile是否还有其他axn关系，这些x号不能再使用
    find_used_telx_sql = text("""
        select
            distinct tel_x
        from
            virtual_numbers
        where
            tel_b=''
            and valid=1;
    """)

    # 查找可用的虚拟号
    find_usable_telx_sql = """
        select
            tel
        from
            number_pool_for_lt
        where
            deleted_at=0
            and enterprise_id=0
            and for_sms=false
            {used_tel_x_condition}
            {area_condition}
        order by
            use_count asc,
            updated_at asc;
    """

    add_virtual_number_use_count_sql = text("""
        update
            number_pool_for_lt
        set
            use_count=use_count+1,
            updated_at=UNIX_TIMESTAMP()
        where
            tel=:tel;
    """)

    # 写入axn关系
    insert_virtual_number_sql = text("""
        insert into virtual_numbers(
            tel_a, tel_x, created_at, updated_at, tel_b
        )values(
            :tel_a, :tel_x, UNIX_TIMESTAMP(), UNIX_TIMESTAMP(), ''
        );
    """)

    find_last_virtual_number_id_sql = text("""
        select LAST_INSERT_ID();
    """)

    # 更新outbound信息
    update_outbound_record_sql = text("""
        update
            outbound_records
        set
            virtual_number_id=:virtual_number_id
        where
            id=:outbound_record_id;
    """)

    # 先查询是否已有有效绑定记录
    existed_data = session.execute(
        find_existed_valid_sql,
        {
            'tel_a': mobile
        }
    ).fetchone()
    if existed_data:
        return existed_data.tel_x, existed_data.id

    # 查找该mobile对应的user，获取地区信息
    user_info = session.execute(
        find_user_sql,
        dict(
            mobile=mobile,
        )
    ).fetchone()
    if user_info:
        prov_id = str(user_info.area_id)[:2] + '0000'
    else:
        # 没有对应账户，默认使用广东的虚拟号
        prov_id = '440000'
    area_ids = TEL_X_AREA_MAP.get(prov_id)

    # 查找不可使用的虚拟号
    used_telx_list = session.execute(
        find_used_telx_sql,
        dict(
            tel_a=mobile,
        )
    ).fetchall()
    used_telx = tuple([i.tel_x for i in used_telx_list])

    area_condition = 'and area_id in :area_ids' if area_ids else ''
    used_tel_x_condition = 'and tel not in :used_telx' if used_telx else ''

    area_usable_tel_x_sql = text(
        find_usable_telx_sql.format(
            area_condition=area_condition,
            used_tel_x_condition=used_tel_x_condition
        )
    )

    usable_telx = session.execute(
        area_usable_tel_x_sql,
        dict(used_telx=used_telx, area_ids=area_ids)
    ).fetchone()

    if usable_telx:
        session.execute(
            insert_virtual_number_sql,
            dict(tel_a=mobile, tel_x=usable_telx.tel)
        )

        last_virtual_number_id = session.execute(
            find_last_virtual_number_id_sql).fetchone()[0]

        session.execute(
            update_outbound_record_sql,
            dict(
                virtual_number_id=last_virtual_number_id,
                outbound_record_id=outbound_record.id
            )
        )

        session.execute(
            add_virtual_number_use_count_sql,
            dict(tel=usable_telx.tel)
        )
        return usable_telx.tel, last_virtual_number_id
    else:
        return ''


if __name__ == '__main__':
    # action_id = uuid.uuid4().hex
    # resp = Agent.dialout('8004', '13430607063', action_id)
    # print(resp)
    # print(resp.get('Succeed'))
    # print(not resp.get('Succeed'))

    #print(WebCall.bridge_web_call(
    #    Exten='18817877341',
    #    Variable='13174810407',
    #))

    #print(Agent.query_idle_agent_num())
    Agent.list_agent_state()
    # print(Agent.dialout('8000', '15610498231'))
